﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OpenSSL.X509;
using System.Data.OleDb;
using Sicurezza.CA.Entita;

namespace Sicurezza.CA.DataProvider
{
    public class ProfileDataProvider
    {
        public int GetId(X509Name n)
        {
           int id = 0;

            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                conn.Open();

                string query = "SELECT Id " +
                               "FROM Profiles " +
                               "WHERE " + getSelectValues(n);

                OleDbCommand cmd = new OleDbCommand(query, conn);
                id = Convert.ToInt32(cmd.ExecuteScalar());

            }

            return id;
        }

        private string getSelectValues(X509Name n)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("C='" + n.Country + "'");
            sb.Append(" AND ");
            sb.Append("O='" + n.Organization + "'");
            sb.Append(" AND ");
            sb.Append("OU='" + n.OrganizationUnit + "'");
            sb.Append(" AND ");
            sb.Append("CN='" + n.Common + "'");


            return sb.ToString();
        }

        public int Save(X509Name x509Name)
        {
            int id = 0;

            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                conn.Open();

                string query = "INSERT INTO Profiles " +
                               "(C,O,OU,CN,Email) " +
                               "VALUES (" + getInsertValues(x509Name) + ")";

                OleDbCommand cmd = new OleDbCommand(query, conn);
                cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT @@Identity";
                id = Convert.ToInt32(cmd.ExecuteScalar());

            }

            return id;
        }

        private string getInsertValues(X509Name x509Name)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("'" + x509Name.Country + "'");
            sb.Append(",");
            sb.Append("'" + x509Name.Organization + "'");
            sb.Append(",");
            sb.Append("'" + x509Name.OrganizationUnit + "'");
            sb.Append(",");
            sb.Append("'" + x509Name.Common + "'");
            sb.Append(",");
            sb.Append("'" + x509Name.GetTextByName("subjectAltName").Substring(6) + "'");

            return sb.ToString();
        }

        public X509Name Get(int CertificateSerial)
        {
            X509Name n = null;

            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                conn.Open();

                string query = "SELECT * "+
                               "FROM Profiles p INNER JOIN Certificates c on p.id = c.ProfileId " +
                               "WHERE c.id =" + CertificateSerial;

                OleDbCommand cmd = new OleDbCommand(query, conn);
                OleDbDataReader dr = cmd.ExecuteReader();

                if (dr.Read())
                {
                    n = BuildObject(dr);
                }

            }

            return n;
        }

        private X509Name BuildObject(OleDbDataReader dr)
        {
            X509Name n = new X509Name();
            n.Common = Convert.ToString(dr["CN"]).Trim();
            n.Organization = Convert.ToString(dr["O"]).Trim();
            n.OrganizationUnit = Convert.ToString(dr["OU"]).Trim();
            n.Country = Convert.ToString(dr["C"]).Trim();
            n.AddEntryByName("subjectAltName", Convert.ToString(dr["Email"]).Trim());
            return n;
        }

        public List<Profile> GetAll()
        {
            List<Profile> prof = new List<Profile>();

            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                conn.Open();

                string query = "SELECT * " +
                               "FROM Profiles p INNER JOIN Certificates c on p.id = c.ProfileId ";

                OleDbCommand cmd = new OleDbCommand(query, conn);
                OleDbDataReader dr = cmd.ExecuteReader();

                if(dr.HasRows)
                {
                    prof = BuildProfiles(dr);
                }

            }

            return prof;
        }

        private List<Profile> BuildProfiles(OleDbDataReader dr)
        {
            Dictionary<int, Profile> profiles = new Dictionary<int, Profile>();
            while (dr.Read())
            {
                int id = Convert.ToInt32(dr["p.id"]);
                if (profiles.ContainsKey(id))
                {
                    Profile p = profiles[id];
                    p.DigitalSignature =p.DigitalSignature || Convert.ToBoolean(dr["DigitalSignature"]);
                    p.KeyEncipherment = p.KeyEncipherment || Convert.ToBoolean(dr["KeyEncipherment"]);
                }
                else
                {
                    Profile p = new Profile();
                    p.DigitalSignature = Convert.ToBoolean(dr["DigitalSignature"]);
                    p.KeyEncipherment = Convert.ToBoolean(dr["KeyEncipherment"]);
                    p.EMail = Convert.ToString(dr["Email"]).Trim();
                    p.DistinguishedName = "C=" + Convert.ToString(dr["C"]).Trim() + ", ";
                    p.DistinguishedName += "O=" + Convert.ToString(dr["O"]).Trim() + ", ";
                    p.DistinguishedName += "OU=" + Convert.ToString(dr["OU"]).Trim() + ", ";
                    p.DistinguishedName += "CN=" + Convert.ToString(dr["CN"]).Trim();
                    profiles.Add(id, p);
                }
            }

            return profiles.Values.ToList();
        }
    }
}
